﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

using Microsoft.ApplicationBlocks.Data;

using YouHP.IDAL;
using YouHP.Model;
using YouHP.Utility;
using System.Data.SqlClient;

namespace YouHP.SqlServerDAL
{
    public class PatientSymptomDAL : IPatientSymptom
    {
        #region private functions
        // 添加单条的症状历史记录,采用事务方式
        private int AddSymptomHistory(SqlTransaction ts, SymptomHistoryInfo shInfo)
        {
            //判断当前对应的记录是否已经存在，如存在，则执行更新操作
            int shId = GetSymptomHistory(ts, shInfo.PRId, shInfo.SymptomId, shInfo.RecordDate);
            if (shId > 0)
            {
                //执行update操作，覆盖原来的数据
                shInfo.Id = shId;
                UpdateSymptomHistory(ts, shInfo);
                return shId;
            }

            SqlParameter[] outParms = new SqlParameter[]  {
                new SqlParameter("@PRId", SqlDbType.Int),
                new SqlParameter("@SymptomId", SqlDbType.Int),
                new SqlParameter("@SurveyId", SqlDbType.Int),
                new SqlParameter("@DegreeValue", SqlDbType.Int),
                new SqlParameter("@RecordDate", SqlDbType.DateTime),
                new SqlParameter("@UpdateDate", SqlDbType.DateTime),
                new SqlParameter("@Remark", SqlDbType.NVarChar, 2000),
                new SqlParameter("@Type", SqlDbType.Int),
                new SqlParameter("@Id", SqlDbType.Int)
            };
            outParms[0].Value = shInfo.PRId;
            outParms[1].Value = shInfo.SymptomId;
            outParms[2].Value = shInfo.SurveyId;
            outParms[3].Value = shInfo.DegreeValue;
            outParms[4].Value = shInfo.RecordDate;
            outParms[5].Value = shInfo.UpdateDate;
            outParms[6].Value = shInfo.Remark;
            outParms[7].Value = shInfo.Type;
            outParms[8].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(ts, CommandType.StoredProcedure, Common.PatientSymptomSP_AddSymptomHistory, outParms);

            return Convert.ToInt32(outParms[8].Value.ToString());
        }

        // 修改单条的症状历史记录,采用事务方式
        private void UpdateSymptomHistory(SqlTransaction ts, SymptomHistoryInfo info)
        {
            SqlHelper.ExecuteNonQuery(ts,
                Common.PatientSymptomSP_UpdateSymptomHistory,
                info.Id,
                info.PRId,
                info.SymptomId,
                info.SurveyId,
                info.DegreeValue,
                info.RecordDate,
                info.UpdateDate,
                info.Remark
                );

            return;
        }

        // 删除当前记录表中的纪录,采用事务方式
        private bool DeleteCurrentSymptom(SqlTransaction ts, int prId, int symptomId)
        {
            string condStr = string.Format(@" [PatientCurrentSymptom].[PRId]={0} AND [PatientCurrentSymptom].[SymptomId]={1}",
                prId, symptomId);

            return SqlHelper.ExecuteNonQuery(ts,
                Common.PatientSymptomSP_DelCurrentSymptom,
                condStr.ToString()) > 0;
        }

        // 添加当前的症状记录, 采用事务方式
        private void AddCurrentSymptom(SqlTransaction ts, SymptomHistoryInfo info)
        {
            SqlHelper.ExecuteNonQuery(ts,
                Common.PatientSymptomSP_AddCurrentSymptom,
                info.PRId,
                info.SymptomId,
                info.DegreeValue,
                info.SymptomHistoryId
                );

            return;
        }

        // 修改当前的症状记录, 采用事务方式
        private void UpdateCurrentSymptom(SqlTransaction ts, SymptomHistoryInfo info)
        {
            SqlHelper.ExecuteNonQuery(ts,
                Common.PatientSymptomSP_UpdateCurrentSymptom,
                info.PRId,
                info.SymptomId,
                info.DegreeValue,
                info.SymptomHistoryId
                );

            return;
        }

        // 根据历史Id删除症状历史记录信息,采用事务方式
        private bool DeleteSymptomHistory(SqlTransaction ts, int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientSymptomHistory].[PRId]={0} AND [PatientSymptomHistory].[Id]={1}",
                prId, historyId);

            return SqlHelper.ExecuteNonQuery(ts,
                Common.PatientSymptomSP_DelSymptomHistory,
                condStr.ToString()) > 0;
        }

        // 检查当前对应的记录是否已经存在
        private int GetSymptomHistory(SqlTransaction ts, int prId, int symptomId, DateTime recordDate)
        {
            string condStr = string.Format(@" [PatientSymptomHistory].[PRId]={0} AND [PatientSymptomHistory].[SymptomId]={1} AND "
                + " [PatientSymptomHistory].[RecordDate]='{2}' ", prId, symptomId, recordDate);

            IDataReader reader = SqlHelper.ExecuteReader(ts,
                Common.PatientSymptomSP_GetSymptomHistory,
                condStr) as IDataReader;
            SymptomHistoryInfo shInfo = CBO.FillObject<SymptomHistoryInfo>(reader);

            return shInfo.Id;
        }

        // 取得记录表中最早的一条
        private SymptomHistoryInfo GetLasttestSymptomHistory(SqlTransaction ts, int prId, int symptomId)
        {
            string condStr = string.Format(@" [PatientSymptomHistory].[PRId]={0} AND [PatientSymptomHistory].[SymptomId]={1} AND "
                + " [PatientSymptomHistory].[RecordDate]=( SELECT MAX(RecordDate) from PatientSymptomHistory "
                + " WHERE [PatientSymptomHistory].[PRId] = {0} AND [PatientSymptomHistory].[SymptomId]={1} ) ", prId, symptomId);

            IDataReader reader = SqlHelper.ExecuteReader(ts,
                Common.PatientSymptomSP_GetSymptomHistory,
                condStr) as IDataReader;
            return CBO.FillObject<SymptomHistoryInfo>(reader);
        }

        // 根据纪录时间来判断当前记录是否为历史纪录中最早的纪录
        private bool CheckLasttestSymptomHistory(SqlTransaction ts, int prId, int symptomId, DateTime recordDate)
        {
            bool retVal = true;

            string condStr = string.Format(@" [PatientSymptomHistory].[PRId]={0} AND [PatientSymptomHistory].[SymptomId]={1} AND "
                + " [PatientSymptomHistory].[RecordDate] > '{2}' ", prId, symptomId, recordDate);

            IDataReader dr = SqlHelper.ExecuteReader(ts,
                Common.PatientSymptomSP_GetSymptomHistoryCount,
                condStr);

            if (dr != null && dr.Read())
            {
                if (dr.GetInt32(0) > 0)
                {
                    retVal = false; //存在则说明,该记录不是最新的记录
                }
                dr.Close();
            }

            return retVal;
        }

        // 判断当前纪录表中是否已存在纪录
        private bool CheckExsitCurrentSymptomHistory(SqlTransaction ts, int prId, int symptomId)
        {
            bool retVal = false;

            string condStr = string.Format(@" [PatientCurrentSymptom].[PRId]={0} AND [PatientCurrentSymptom].[SymptomId]={1} ",
                prId, symptomId);

            IDataReader dr = SqlHelper.ExecuteReader(ts,
                Common.PatientSymptomSP_GetCurrentSymptomCount,
                condStr);

            if (dr != null && dr.Read())
            {
                if (dr.GetInt32(0) > 0)
                {
                    retVal = true;
                }
                dr.Close();
            }

            return retVal;
        }

        // 在当前记录表查找某条历史纪录
        private SymptomHistoryInfo GetCurrentSymptom(SqlTransaction ts, int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientCurrentSymptom].[PRId]={0} AND [PatientCurrentSymptom].[SymptomHistoryId]={1}",
                prId, historyId);

            IDataReader reader = SqlHelper.ExecuteReader(ts,
                Common.PatientSymptomSP_GetCurrentSymptom,
                condStr) as IDataReader;
            return CBO.FillObject<SymptomHistoryInfo>(reader);
        }

        // 判断当前纪录表中是否已存在纪录
        private bool CheckExsitCurrentSymptomHistoryByHistoryId(SqlTransaction ts, int prId, int historyId)
        {
            bool retVal = false;

            string condStr = string.Format(@" [PatientCurrentSymptom].[PRId]={0} AND [PatientCurrentSymptom].[SymptomHistoryId]={1} ",
                prId, historyId);

            IDataReader dr = SqlHelper.ExecuteReader(ts,
                Common.PatientSymptomSP_GetCurrentSymptomCount,
                condStr);

            if (dr != null && dr.Read())
            {
                if (dr.GetInt32(0) > 0)
                {
                    retVal = true;
                }
                dr.Close();
            }

            return retVal;
        }

        // 获得批量的症状历史记录,采用事务的方式
        private List<SymptomHistoryInfo> GetSymptomSurvey(SqlTransaction ts, int prId, int surveyId)
        {
            string condStr = string.Format(@" [PatientSymptomHistory].[PRId]={0} AND [PatientSymptomHistory].[SurveyId]={1} ",
                prId, surveyId);

            IDataReader dr = SqlHelper.ExecuteReader(ts,
                Common.PatientSymptomSP_GetSurveyHistorySymptomList,
                condStr);

            return CBO.FillCollection<SymptomHistoryInfo>(dr);
        }
        #endregion

        //判断历史记录表中是否已存在病人指定时间对应的记录
        public bool CheckSymptomHistory(int prId, int symptomId, DateTime recordDate)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                int shId = GetSymptomHistory(ts, prId, symptomId, recordDate);
                if (shId > 0)
                {
                    retVal = true;
                }
                    
                ts.Commit();
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 取得病人的某种症状的当前程度
        public SymptomHistoryInfo GetCurrentSymptom(int prId, int symptomId)
        {
            string condStr = string.Format(@" [PatientCurrentSymptom].[PRId]={0} AND [PatientCurrentSymptom].[SymptomId]={1} ",
                prId, symptomId); 

            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSymptomSP_GetCurrentSymptom,
                condStr) as IDataReader;
            return CBO.FillObject<SymptomHistoryInfo>(reader);
        }

        // 添加单条的症状历史记录
        public bool AddSymptomHistory(SymptomHistoryInfo info)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                info.SymptomHistoryId = AddSymptomHistory(ts, info);

                //判断当前记录是否为历史纪录中最早的纪录，如是则需要更新当前纪录表，否则不用处理
                if (CheckLasttestSymptomHistory(ts, info.PRId, info.SymptomId, info.RecordDate))
                {
                    //判断当前表中是否存在记录，存在则需要更新当前的记录值，否则执行添加
                    if (CheckExsitCurrentSymptomHistory(ts, info.PRId, info.SymptomId))
                    {
                        //执行修改
                        UpdateCurrentSymptom(ts, info);
                    }
                    else
                    {
                        //执行添加
                        AddCurrentSymptom(ts, info);
                    }
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 根据历史Id获得症状历史记录信息
        public SymptomHistoryInfo GetSymptomHistory(int prId, int historyId)
        {
            string condStr = string.Format(@" [PatientSymptomHistory].[PRId]={0} AND [PatientSymptomHistory].[Id]={1}",
                prId, historyId);

            IDataReader reader = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSymptomSP_GetSymptomHistory,
                condStr) as IDataReader;
            return CBO.FillObject<SymptomHistoryInfo>(reader);
        }

        // 修改单条的症状历史记录
        public bool UpdateSymptomHistory(SymptomHistoryInfo info)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                UpdateSymptomHistory(ts, info);
                info.SymptomHistoryId = info.Id;    //为操作当前记录准备数据

                //判断当前记录是否为历史纪录中最早的纪录，如是则需要更新当前纪录表，否则不用处理
                if (CheckLasttestSymptomHistory(ts, info.PRId, info.SymptomId, info.RecordDate))
                {
                    //判断当前表中是否存在记录，存在则需要更新当前的记录值，否则执行添加
                    if (CheckExsitCurrentSymptomHistory(ts, info.PRId, info.SymptomId))
                    {
                        //执行修改
                        UpdateCurrentSymptom(ts, info);
                    }
                    else
                    {
                        //执行添加
                        AddCurrentSymptom(ts, info);
                    }
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 删除单条症状历史记录信息(SurveyId = 0)
        public bool DeleteSymptomHistory(int prId, int historyId)
        {
            bool retVal = false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                //在当前记录表查找该历史纪录(最好是先取,再删除)
                SymptomHistoryInfo shInfo = GetCurrentSymptom(ts, prId, historyId);
                //从历史纪录表中删除该纪录
                DeleteSymptomHistory(ts, prId, historyId);

                if (shInfo.SymptomId > 0)   //需要对当前记录进行操作
                {
                    //从历史纪录中再选出一条最新的记录
                    SymptomHistoryInfo lasttestInfo = GetLasttestSymptomHistory(ts, prId, shInfo.SymptomId);
                    if (lasttestInfo.Id <= 0)   //如果找不到,则将当前记录表中的内容删除
                    {
                        DeleteCurrentSymptom(ts, prId, shInfo.SymptomId);
                    }
                    else  //如果找到,则修改当前记录表中的数据
                    {
                        lasttestInfo.SymptomHistoryId = lasttestInfo.Id;
                        UpdateCurrentSymptom(ts, lasttestInfo);
                    }
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 批量的症状历史记录提交
        public bool AddSymptomSurvey(List<SymptomHistoryInfo> shList)
        {
            bool retVal = false;
            int surveyId = -1;
            if (shList.Count <= 0)
                return false;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                //添加survey表
                PatientSurveyInfo psInfo = new PatientSurveyInfo();
                psInfo.PRId = shList[0].PRId;
                psInfo.RecordDate = shList[0].RecordDate;
                psInfo.UpdateDate = shList[0].UpdateDate;
                psInfo.SurveyType = Constants.SymptomSurvey;

                surveyId = Common.AddPatientSurvey(ts, psInfo);
                if (surveyId > 0)
                {
                    //批量添加症状历史记录表
                    for (int i = 0; i < shList.Count; i++)
                    {
                        shList[i].SurveyId = surveyId;
                        shList[i].SymptomHistoryId = AddSymptomHistory(ts, shList[i]);

                        //判断当前记录是否为历史纪录中最早的纪录，如是则需要更新当前纪录表，否则不用处理
                        if (CheckLasttestSymptomHistory(ts, shList[i].PRId, shList[i].SymptomId, shList[i].RecordDate))
                        {
                            //判断当前表中是否存在记录，存在则需要更新当前的记录值，否则执行添加
                            if (CheckExsitCurrentSymptomHistory(ts, shList[i].PRId, shList[i].SymptomId))
                            {
                                //执行修改
                                UpdateCurrentSymptom(ts, shList[i]);
                            }
                            else
                            {
                                //执行添加
                                AddCurrentSymptom(ts, shList[i]);
                            }
                        }
                    }

                    ts.Commit();
                    retVal = true;
                }
                else
                {
                    ts.Rollback();
                }
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 获得批量的症状历史记录
        public List<SymptomHistoryInfo> GetSymptomSurvey(int prId, int surveyId)
        {
            string condStr = string.Format(@" [PatientSymptomHistory].[PRId]={0} AND [PatientSymptomHistory].[SurveyId]={1} ",
                prId, surveyId);

            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSymptomSP_GetSurveyHistorySymptomList,
                condStr);

            return CBO.FillCollection<SymptomHistoryInfo>(dr);
        }

        // 批量的症状历史记录修改
        public bool UpdateSymptomSurvey(int surveyId, List<SymptomHistoryInfo> shList)
        {
            bool retVal = false;
            if(shList.Count <= 0) 
                return false ;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                //更新survey表
                PatientSurveyInfo psInfo = new PatientSurveyInfo() ;
                psInfo.Id = surveyId;
                psInfo.PRId = shList[0].PRId;
                psInfo.RecordDate = shList[0].RecordDate;
                psInfo.UpdateDate = shList[0].UpdateDate;

                if (Common.UpdatePatientSurvey(ts, psInfo))
                {
                    //批量更新症状历史记录表
                    for(int i=0; i<shList.Count; i++)
                    {
                        shList[i].SurveyId = surveyId;
                        UpdateSymptomHistory(ts, shList[i]);
                        shList[i].SymptomHistoryId = shList[i].Id;    //为操作当前记录准备数据

                        //判断当前记录是否为历史纪录中最早的纪录，如是则需要更新当前纪录表，否则不用处理
                        if(CheckLasttestSymptomHistory(ts, shList[i].PRId, shList[i].SymptomId, shList[i].RecordDate))
                        {
                            //判断当前表中是否存在记录，存在则需要更新当前的记录值，否则执行添加
                            if (CheckExsitCurrentSymptomHistory(ts, shList[i].PRId, shList[i].SymptomId))
                            {
                                //执行修改
                                UpdateCurrentSymptom(ts, shList[i]);
                            }
                            else
                            {
                                //执行添加
                                AddCurrentSymptom(ts, shList[i]);
                            }
                        }

                        //如果当前记录的记录时间被修改,调整到非最新的日期,则需要重新更新当前记录表
                    }

                    ts.Commit();
                    retVal = true;
                }
                else
                {
                    ts.Rollback();
                }
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 根据SurveyId删除症状历史记录信息
        public bool DeleteSymptomSurvey(int prId, int surveyId)
        {
            bool retVal = false ;
            int historyId = -1;

            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            try
            {
                // 从历史纪录中中检索出surveyId对应的所有记录信息
                List<SymptomHistoryInfo> shList = GetSymptomSurvey(ts, prId, surveyId);

                // 遍历每条记录判断是否出现在当前表中,
                foreach (SymptomHistoryInfo info in shList)
                {
                    historyId = info.Id;
                    //在当前记录表查找该历史纪录(最好是先取,再删除)
                    SymptomHistoryInfo shInfo = GetCurrentSymptom(ts, prId, historyId);
                    //从历史纪录表中删除该纪录
                    DeleteSymptomHistory(ts, prId, historyId);

                    if (shInfo.SymptomId > 0)   //需要对当前记录进行操作
                    {
                        //从历史纪录中再选出一条最新的记录
                        SymptomHistoryInfo lasttestInfo = GetLasttestSymptomHistory(ts, prId, shInfo.SymptomId);
                        if (lasttestInfo.Id <= 0)   //如果找不到,则将当前记录表中的内容删除
                        {
                            DeleteCurrentSymptom(ts, prId, shInfo.SymptomId);
                        }
                        else  //如果找到,则修改当前记录表中的数据
                        {
                            lasttestInfo.SymptomHistoryId = lasttestInfo.Id;
                            UpdateCurrentSymptom(ts, lasttestInfo);
                        }
                    }
                }

                ts.Commit();
                retVal = true;
            }
            catch
            {
                ts.Rollback();
            }
            conn.Close();

            return retVal;
        }

        // 取得病人的当前症状列表
        public List<SymptomHistoryInfo> GetCurrentSymptomList(int prId)
        {
            string condStr = string.Format(@" [PatientCurrentSymptom].[PRId]={0} ", prId);

            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSymptomSP_GetCurrentSymptomList,
                condStr);

            return CBO.FillCollection<SymptomHistoryInfo>(dr);
        }

        // 获得PatientCurrentSymptom表中相关的记录
        public List<SymptomHistoryInfo> GetCurrentSymptomListBySymptomAndDegreeValue(int symptomId, int degreeValue)
        {
            string condStr = string.Format(@" [PatientCurrentSymptom].[SymptomId]={0} ", symptomId);

            if (degreeValue >= 0)
            {
                condStr += string.Format(@" AND [PatientCurrentSymptom].[DegreeValue]={0}", degreeValue);
            }

            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSymptomSP_GetCurrentSymptomList,
                condStr);

            return CBO.FillCollection<SymptomHistoryInfo>(dr);
        }

        //取得某段时间内的所有症状记录历史
        public List<SymptomHistoryInfo> GetSymptomHistoryList(int prId, DateTime startDate, DateTime endDate)
        {
            string condStr = string.Format(@" [PatientSymptomHistory].[PRId]={0} AND [PatientSymptomHistory].[RecordDate] >= '{1}' " +
                " AND [PatientSymptomHistory].[RecordDate] <= '{2}' ", prId, startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
            string orderStr = " [PatientSymptomHistory].[SymptomId] asc, [PatientSymptomHistory].[RecordDate] asc ";

            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr,
                Common.PatientSymptomSP_GetAllSymptomHistory,
                condStr, orderStr);

            return CBO.FillCollection<SymptomHistoryInfo>(dr);           
        }

        // 取得病人针对某种症状的症状记录历史
        public List<SymptomHistoryInfo> GetSymptomHistoryList(int prId, int symId, int pageSize, int pageIndex, out int count)
        {
            string condStr = string.Format(@" [PatientSymptomHistory].[PRId]={0} AND [PatientSymptomHistory].[SymptomId]={1} ", prId, symId);
            DataSet dset = SqlHelper.ExecuteDataset(Common.ConnStr,
                Common.PatientSymptomSP_GetSymptomHistoryList,
                pageSize, pageIndex, condStr, " [PatientSymptomHistory].[RecordDate] desc ");

            IDataReader dr = dset.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }
            dr = dset.Tables[1].CreateDataReader();

            return CBO.FillCollection<SymptomHistoryInfo>(dr);
        }
    }
}
